<?php

// +----------------------------------------------------------------------
// | H1CMS © OpenSource CMS
// +----------------------------------------------------------------------
// | Copyright (c) 2014-2016 http://www.h1cms.com All rights reserved.
// | Copyright (c) 2014-2016 嘉兴领格信息技术有限公司，并保留所有权利。
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: Allen <allen@lg4.cn>
// +----------------------------------------------------------------------

namespace Lg\Database;

use PDO;
use Exception;
use PDOException;
use Lg\Config;
use Lg\Database\Expr;

/**
 * Description of Database 
 * @author Allen <allen@lg4.cn>
 */
class Database {

    /**
     *
     * @var \PDO
     */
    protected $pdo;
// General
    protected $driver;
    protected $charset;
    protected $database;
//db type
    protected $type;
// For MySQL, MariaDB, MSSQL, Sybase, PostgreSQL, Oracle
    protected $host;
    protected $username;
    protected $password;
// For SQLite
    protected $database_file;
// For MySQL or MariaDB with unix_socket
    protected $socket;
// Optional
    protected $port;
    protected $prefix;
    protected $option;
// Variable
    protected $logs = array();
    protected $debug_mode = false;
    public static $instances;

    public function __construct($options = null, $pdoInstance = null) {
        if (!is_null($pdoInstance)) {
            $this->pdo = $pdoInstance;
            return true;
        }
        $this->option = Config::get('database.options');
        try {
            $commands = array();
            $dsn = '';
            if (is_array($options)) {
                foreach ($options as $option => $value) {
                    $this->$option = $value;
                }
            } else {
                return false;
            }
            if (
                    isset($this->port) &&
                    is_int($this->port * 1)
            ) {
                $port = $this->port;
            }
            $type = strtolower($this->driver);
            $is_port = isset($port);
            if (isset($options['prefix'])) {
                $this->prefix = $options['prefix'];
            }
            switch ($type) {
                case 'mariadb':
                    $type = 'mysql';
                case 'mysql':
                    if ($this->socket) {
                        $dsn = $type . ':unix_socket=' . $this->socket . ';dbname=' . $this->database;
                    } else {
                        $dsn = $type . ':host=' . $this->host . ($is_port ? ';port=' . $port : '') . ';dbname=' . $this->database;
                    }
// Make MySQL using standard quoted identifier
                    $commands[] = 'SET SQL_MODE=ANSI_QUOTES';
                    break;
                case 'pgsql':
                    $dsn = $type . ':host=' . $this->host . ($is_port ? ';port=' . $port : '') . ';dbname=' . $this->database;
                    break;
                case 'sybase':
                    $dsn = 'dblib:host=' . $this->host . ($is_port ? ':' . $port : '') . ';dbname=' . $this->database;
                    break;
                case 'oracle':
                    $dbname = $this->host ?
                            '//' . $this->host . ($is_port ? ':' . $port : ':1521') . '/' . $this->database :
                            $this->database;
                    $dsn = 'oci:dbname=' . $dbname . ($this->charset ? ';charset=' . $this->charset : '');
                    break;
                case 'mssql':
                    $dsn = strstr(PHP_OS, 'WIN') ?
                            'sqlsrv:host=' . $this->host . ($is_port ? ',' . $port : '') . ';database=' . $this->database :
                            'dblib:host=' . $this->host . ($is_port ? ':' . $port : '') . ';dbname=' . $this->database;
// Keep MSSQL QUOTED_IDENTIFIER is ON for standard quoting
                    $commands[] = 'SET QUOTED_IDENTIFIER ON';
                    break;
                case 'sqlite':
                    $dsn = $type . ':' . $this->database_file;
                    $this->username = null;
                    $this->password = null;
                    break;
            }
            if (
                    in_array($type, array('mariadb', 'mysql', 'pgsql', 'sybase', 'mssql')) &&
                    $this->charset
            ) {
                $commands[] = "SET NAMES '" . $this->charset . "'";
            }
            $this->type = $type;
            $this->pdo = new PDO(
                    $dsn, $this->username, $this->password, $this->option
            );
            foreach ($commands as $value) {
                $this->pdo->exec($value);
            }
        } catch (PDOException $e) {
            throw new Exception($e->getMessage());
        }
    }

    /**
     * 
     * @return \Lg\Database\Database
     */
    public static function getConnection($target = NULL) {
        if (is_null($target)) {
            $target = Config::get('database.default');
        }
        if (is_null(static::$instances[$target])) {

            static::$instances[$target] = new Database(Config::get('database.connections.' . $target));
        }
        return static::$instances[$target];
    }

    public static function isActive($target = NULL) {
        if (is_null($target)) {
            $target = Config::get('database.default');
        }
        return isset(static::$instances[$target]);
    }

    /**
     * 
     * @param string $query
     * @param array $params
     * @return \PDOStatement
     */
    public function query($query, $params = []) {
        $query = $this->prepareSQL($query);
        $this->logs[] = $query;
        $statement = $this->pdo->prepare($query);
        if (is_scalar($params)) {
            $params = [$params];
        }
        $statement->execute($params);
        return $statement;
    }

    /**
     * 
     * @param string $query
     * @return boolean
     */
    public function exec($query) {
        $query = $this->prepareSQL($query);
        $this->logs[] = $query;
        return $this->pdo->exec($this->prepareSQL($query));
    }

    /**
     * 
     * @param string $value
     * @return string
     */
    public function quote($value) {
        if ($value instanceof Expr) {
            return $value->raw;
        } else if (is_array($value)) {
            return implode(',', array_map(function($val) {
                        return $this->pdo->quote($val);
                    }, $value));
        }

        return $this->pdo->quote($value);
    }

    /**
     * 
     * @param string $table Table Name
     * @return string Table Name
     */
    public function quoteTable($table) {
        switch ($this->type) {
            case 'mysql':
            case 'mariadb':
                return '`' . $table . '`';
            case 'mssql':
                return "[$table]";
            case 'pssql':
                return '"' . $table . '"';
            default:
                return $table;
        }
    }

    public function tableName($name) {
        return $this->prefix . $name;
    }

    /**
     * PDO Class
     * @return \PDO
     */
    public function pdo() {
        return $this->pdo;
    }

    /**
     * Quote Column Name
     * @param string $columnName
     * @return string
     */
    public function quoteColumn($columnName) {
        $colAlias = explode('.', $columnName);
        if (is_array($colAlias) && count($colAlias) == 2) {
            return $this->quoteColumn($colAlias[0]) . '.' . $this->quoteColumn($colAlias[1]);
        }
        switch ($this->type) {
            case 'mysql':
            case 'mariadb':
                return "`$columnName`";
            case 'mssql':
                return "[$columnName]";
            case 'pssql':
                return '"' . $columnName . '"';
            default:
                return $columnName;
        }
    }

    public function insert($tableName, $columns) {
        $params = array();
        $names = array();
        $placeholders = array();
        foreach ($columns as $name => $value) {
            $names[] = $this->quoteColumn($name);
            if ($value instanceof Expr) {
                $placeholders[] = $value->raw;
            } else {
                $bindName = \Lg\Support\Str::xrandom(\Lg\Support\Str::ALPHA);
                $placeholders[] = ':' . $bindName;
                $params[$bindName] = $value;
            }
        }
        $sql = 'INSERT INTO ' . $this->quoteTable($this->tableName($tableName))
                . ' (' . implode(', ', $names) . ') VALUES ('
                . implode(', ', $placeholders) . ')';
        $statement = $this->prepare($sql);
        $statement->execute($params);
        return $this->lastId();
    }

    public function batchInsert($table, $columns, $rows) {
        $values_placeholder = str_repeat('?,', count($columns) - 1) . '?';
        $columns = join(',', array_map(function($column) {
                    return $this->quoteColumn($column);
                }, $columns));
        $statement = $this->prepare("INSERT INTO {{$table}} ({$columns}) VALUES ({$values_placeholder})");
        foreach ($rows as $row) {
            $statement->execute($row);
        }
    }

    public function update($table, $columns, $conditions = '', $params = array()) {
        $placeholders = array();
        $input_params = array();
        foreach ($columns as $name => $value) {
            if ($value instanceof Expr) {
                $placeholders[] = $this->quoteColumn($name) . '=' . $value->raw;
            } else {
                $placeholders[] = $this->quoteColumn($name) . '=:' . $name;
                $input_params[$name] = $value;
            }
        }

        $sql = 'UPDATE ' . $this->quoteTable($this->tableName($table)) . ' SET ' . implode(', ', $placeholders);
        if (($where = $this->prepareConditions($conditions, $params, $input_params)) != '') {
            $sql.=' WHERE ' . $where;
        }
        $statement = $this->prepare($sql);
        $statement->execute($input_params);
        return $statement->rowCount();
    }

    public function delete($table, $conditions = '', $params = array()) {
        $queryStr = 'DELETE FROM ' . $this->quoteTable($this->tableName($table));
        $input_params = array();
        if (($where = $this->prepareConditions($conditions, $params, $input_params)) != '') {
            $queryStr.=' WHERE ' . $where;
        }
        $statement = $this->prepare($queryStr);
        $statement->execute($input_params);
        return $statement->rowCount();
    }

    public function count($table, $conditions = '', $params = array()) {
        $queryStr = 'SELECT COUNT(*) as rowcount FROM ' . $this->quoteTable($this->tableName($table));
        $input_params = array();
        if (($where = $this->prepareConditions($conditions, $params, $input_params)) != '') {
            $queryStr.=' WHERE ' . $where;
        }
        $statement = $this->prepare($queryStr);
        $statement->execute($input_params);
        return $statement->fetchColumn(0);
    }

    public function dict($table, $columns, $conditions = '', $params = array()) {
        if (is_array($columns)) {
            $columns = join(',', array_map(function($value) {
                        return $this->quoteColumn($value);
                    }, $columns));
        }
        $queryStr = 'SELECT ' . $columns . ' FROM ' . $this->quoteTable($this->tableName($table));
        $input_params = array();
        if (($where = $this->prepareConditions($conditions, $params, $input_params)) != '') {
            $queryStr.=' WHERE ' . $where;
        }
        $statement = $this->prepare($queryStr);
        $statement->execute($input_params);
        $keypairs = [];
        while ($row = $statement->fetch(PDO::FETCH_NUM)) {
            $keypairs[$row[0]] = $row[1];
        }
        unset($statement);
        return $keypairs;
    }

    public function fetchPairs($table, $columns, $conditions = '', $params = array()) {
        return $this->dict($table, $columns, $conditions, $params);
    }

    public function transaction($callable) {
        if (is_callable($callable)) {
            $this->pdo->beginTransaction();
            $result = $callable($this);
            if ($result === false) {
                $this->pdo->rollBack();
            } else {
                $this->pdo->commit();
            }
            return $result;
        } else {
            return false;
        }
    }

    public function debug() {
        $this->debug_mode = true;
        return $this;
    }

    public function error() {
        return $this->pdo->errorInfo();
    }

    public function last_query() {
        return end($this->logs);
    }

    public function log() {
        return $this->logs;
    }

    public function info() {
        $output = array(
            'host' => 'SERVER_INFO',
            'driver' => 'DRIVER_NAME',
            'client' => 'CLIENT_VERSION',
            'version' => 'SERVER_VERSION',
            'connection' => 'CONNECTION_STATUS'
        );
        foreach ($output as $key => $value) {
            $output[$key] = $this->pdo->getAttribute(constant('PDO::ATTR_' . $value));
        }
        return $output;
    }

    /**
     * 
     * @param type $statement
     * @param type $driver_options
     * @return \PDOStatement
     */
    public function prepare($statement, $driver_options = array()) {
        $statement = $this->prepareSQL($statement);
        $this->logs[] = $statement;
        return $this->pdo->prepare($statement, $driver_options);
    }

    public function lastId($name = null) {
        return $this->pdo->lastInsertId($name);
    }

    public function begin() {
        return $this->pdo->beginTransaction();
    }

    public function rollBack() {
        return $this->pdo->rollBack();
    }

    public function commit() {
        return $this->pdo->commit();
    }

    public function setAttribute($attribute, $value) {
        return $this->pdo->setAttribute($attribute, $value);
    }

    public function getAll($query, $params = [], $fetch_style = PDO::FETCH_OBJ) {
        return $this->query($query, $params)->fetchAll($fetch_style);
    }

    public function getRow($query, $params = [], $fetch_style = PDO::FETCH_OBJ) {
        return $this->query($query, $params)->fetch($fetch_style);
    }

    public function getValue($query, $params = array()) {
        return $this->query($query, $params)->fetchColumn(0);
    }

    public function getCol($query, $params = array()) {
        $stm = $this->prepare($query);
        $stm->execute($params);
        return $stm->fetchAll(\PDO::FETCH_COLUMN, 0);
    }

    public function renameTable($oldName, $newName) {
        return $this->exec('RENAME TABLE ' . $this->quoteTable($this->tableName($oldName)) . ' TO ' . $this->quoteTable($this->tableName($newName)));
    }

    public function dropTable($table) {
        return $this->exec('DROP TABLE ' . $this->quoteTable($this->tableName($table)));
    }

    public function truncateTable($table) {
        return $this->exec('TRUNCATE TABLE ' . $this->quoteTable($this->tableName($table)));
    }

    public function prepareSQL($sql) {
        return preg_replace_callback(
                '/(\\{(%?[\w\-\. ]+%?)\\}|\\[([\w\-\. ]+)\\])/',
                function ($matches) {
            if (isset($matches[3])) {
                return $this->quoteColumn($matches[3]);
            } else {
                return $this->quoteTable($this->tableName($matches[2]));
            }
        }, $sql
        );
    }

    private function prepareConditions($conditions, $params = array(), &$input_params) {
        if (is_array($conditions)) {
            $lines = array();
            foreach ($conditions as $name => $value) {
                if ($value instanceof Expr) {
                    $lines[] = $this->quoteColumn($name) . '=' . $value->raw;
                } else {
                    $bindName = \Lg\Support\Str::xrandom(\Lg\Support\Str::ALPHA);
                    $lines[] = $this->quoteColumn($name) . '=:' . $bindName;
                    $input_params[$bindName] = $value;
                }
            }
            return implode(' AND ', $lines);
        } else if (is_string($conditions) && is_array($params) && is_assoc($params)) {
            foreach ($params as $name => $value) {
                if ($value instanceof Expr) {
                    $input_params[$name] = $value->raw;
                }
                $input_params[$name] = $value;
            }
            return $conditions;
        } else if (is_string($conditions) && ( is_scalar($params) || (is_array($params) && !is_assoc($params)))) {
            if (is_scalar($params)) {
                $params = array($params);
            }
            $input_params += $params;
            return $conditions;
        }
        return '';
    }

}
